package com.fsnbigdata.repository;

import com.fsnbigdata.domain.ZhKopp;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

/**
 * Created by Administrator on 2018/8/18.
 */
public interface ZhKoppRepository  extends JpaRepository<ZhKopp,Long>{

    Page<ZhKopp> findAll(Specification<ZhKopp> spec, Pageable pageable);
    
    
//    /**
//     * 查询所有的界
//     * @return
//     */
//    @Query(value = "select p from ZhKopp p where p.parent_id = 0")
//    public abstract List<ZhKopp> findAllKing();
    
//    /**
//     *
//     * @param parent_id
//     * @return
//     */
//    @Query(value = "select p from ZhKopp p where p.parent_id = ?1")
//    public abstract List<ZhKopp> findByParent_id(long parent_id);

    @Query(value = "select p from ZhKopp p where p.initial=?1")
    List<ZhKopp> queryByAlphabet(String s);

    //获取界
    @Query(value = "select id,kingdom,kingdom_en from zh_kopp group by kingdom",nativeQuery = true )
    public  List<Object> findAllByKingdomAndKingdomLatin();

    //根据界获取门
    @Query(value = "select id,phylumn,phylumn_en from zh_kopp where kingdom = ?1 group by phylumn",nativeQuery = true)
    public  List<Object> findAllByKingdom(String kingdom);

    //根据界、门获取纲
    @Query(value = "select id,t_class,t_class_en from zh_kopp where kingdom = ?1 AND phylumn = ?2 group by t_class",nativeQuery = true)
    public  List<Object> findAllByPhylumn(String kingdom,String phylumn);

    //根据界、门、纲获取目
    @Query(value = "select id,t_order,t_order_en from zh_kopp where kingdom = ?1 AND phylumn = ?2 AND t_class = ?3 group by t_order",nativeQuery = true)
    public  List<Object> findByTClass(String kingdom,String phylumn,String tClass);

    //根据界、门、纲、目获取科
    @Query(value = "select id,family,family_en from zh_kopp where kingdom = ?1 AND phylumn = ?2 AND t_class = ?3 AND t_order = ?4 group by family",nativeQuery = true)
    public  List<Object> getByTOrder(String kingdom,String phylumn,String tClass,String tOrder);

    @Query(value ="select * from zh_kopp where if(?1!='',kingdom =?1,1=1) and if(?2!='',phylumn = ?2,1=1) and if(?3!='',t_class = ?3,1=1) and if(?4!='',t_order =?4,1=1) and if(?5!='',z_name =?5,1=1)",nativeQuery = true)
    List<ZhKopp> getData(String kingdom, String phylumn, String t_class, String t_order,String z_name);
}
